﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace QuanTriHeThongKhoDiaChinh
{
    public partial class frmPhanQuyen : Form
    {
        public frmPhanQuyen()
        {
            InitializeComponent();
        }

        SqlCommand cmd;
        TaoKetNoi objTaoKetNoi =new TaoKetNoi();
        SqlConnection con = TaoKetNoi.conn;
        DataTable dt;
        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                //con = objTaoKetNoi.conn;
                con.Open();
                LoadGridview();
                dt = LoadData("SELECT QuyenHanID, TenQuyenHan FROM DMQuyenHan");
                cbQuyenHan.DataSource = dt;
                cbQuyenHan.ValueMember = "QuyenHanID";
                cbQuyenHan.DisplayMember = "TenQuyenHan";
                GoiYTimKiem();
            }
            catch (SqlException sqlEx)
            {
                MessageBox.Show("Connect fail ! "+ sqlEx);               
            }
            
        }
        private void GoiYTimKiem()
        {
            string sql = "select tendangnhap from Login";
            cmd = new SqlCommand(sql, con);
            SqlDataReader reader = cmd.ExecuteReader();
            cmd.Dispose();
            AutoCompleteStringCollection auto = new AutoCompleteStringCollection();
            if (reader != null)
            {
                while (reader.Read())
                {
                    auto.Add(reader["tendangnhap"].ToString());
                }
            }
            txtTimKiem.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            txtTimKiem.AutoCompleteSource = AutoCompleteSource.CustomSource;
            txtTimKiem.AutoCompleteCustomSource = auto;
            reader.Close(); 
        }
      
        private void dgvDanhSach_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (e.RowIndex < 0)
                {
                    return;
                }                
                DataTable dt4 = new DataTable();                
                string tendn = dgvDanhSach.Rows[e.RowIndex].Cells[0].Value.ToString();
                cmd = new SqlCommand("SELECT * FROM Login WHERE TenDangNhap = '" + tendn + "'", con);
                dt4.Load(cmd.ExecuteReader());
                txtTenDangNhap.Text = dt4.Rows[0][1].ToString().Trim();
                txtHoVaTen.Text = dt4.Rows[0][3].ToString().Trim();
                txtMatKhau.Text = dt4.Rows[0][2].ToString().Trim();
                cbQuyenHan.SelectedValue = dt4.Rows[0]["QuyenHanID"].ToString();
                LBTruocPhanQuyen();
                LBSauPhanQuyen();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi ! " + ex);
            }
            
        }
        private DataTable LoadData(string sql)
        {
            cmd = new SqlCommand(sql, con);
            dt = new DataTable();            
            dt.Load(cmd.ExecuteReader());
            return dt;
        }
        private void txtTimKiem_TextChanged(object sender, EventArgs e)
        {
            dt = new DataTable();
            cmd = new SqlCommand("SELECT TenDangNhap, hotenNguoiDangNhap FROM Login WHERE TenDangNhap LIKE '%" + txtTimKiem.Text + "%' ", con);
            dt.Load(cmd.ExecuteReader());
            dgvDanhSach.DataSource = dt;
            
        }

        private void btnThoat_Click(object sender, EventArgs e)
        {
            con.Close();
            Application.Exit();
        }
        private void LBSauPhanQuyen()
        {
            try
            {
                DataTable dt2 = new DataTable();
                DataRow row2 = dt2.NewRow();
                dt = new DataTable();
                cmd = new SqlCommand("SELECT PhanHeCapQuyen FROM Login WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'", con);
                dt.Load(cmd.ExecuteReader());
                string phanheId = dt.Rows[0][0].ToString().Trim();
                char ch;
                for (int i = 0; i < phanheId.Length; i++)
                {
                    ch = phanheId[i];
                    cmd = new SqlCommand("SELECT PhanHeID, TenPhanHe FROM DMPhanHe WHERE PhanHeID = '" + ch + "'", con);
                    dt2.Load(cmd.ExecuteReader());
                    row2["TenPhanHe"] = dt2.Rows[0]["TenPhanHe"].ToString();
                }
                if (dt2.Rows.Count == 0)
                {
                    lbSauPhanQuyen.DataSource = null;
                    return;
                }
                lbSauPhanQuyen.DataSource = dt2;
                lbSauPhanQuyen.DisplayMember = "TenPhanHe";
                lbSauPhanQuyen.ValueMember = "PhanHeID";
                lbSauPhanQuyen.Refresh();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi ! " + ex);
            }
            
        }
        private void LBTruocPhanQuyen()
        {
            try
            {
                DataTable dt3 = new DataTable();
                DataRow row3 = dt3.NewRow();
                dt = new DataTable();
                cmd = new SqlCommand("SELECT PhanHeKhongCapQuyen FROM Login WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'", con);
                dt.Load(cmd.ExecuteReader());
                string phanheId = dt.Rows[0][0].ToString().Trim();
                char ch;
                for (int i = 0; i < phanheId.Length; i++)
                {
                    ch = phanheId[i];
                    cmd = new SqlCommand("SELECT PhanHeID, TenPhanHe FROM DMPhanHe WHERE PhanHeID = '" + ch + "'", con);
                    dt3.Load(cmd.ExecuteReader());
                    row3["TenPhanHe"] = dt3.Rows[0]["TenPhanHe"].ToString();                
                }
                //dt3.Rows.Add(row);
                if (dt3.Rows.Count == 0)
                {
                    lbTruocPhanQuyen.DataSource = null;

                    return;
                }
                lbTruocPhanQuyen.DataSource = dt3;
                lbTruocPhanQuyen.DisplayMember = "TenPhanHe";
                lbTruocPhanQuyen.ValueMember = "PhanHeID";
                lbTruocPhanQuyen.Refresh();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi ! " + ex);
            }
            
        }
        private void txtTenDangNhap_TextChanged(object sender, EventArgs e)
        {
            if (txtTenDangNhap.Text == "")
            {
                return;
            }
        }
        private void LoadGridview()
        { 
                dt = LoadData("SELECT TenDangNhap, hotenNguoiDangNhap FROM Login");
                dgvDanhSach.DataSource = dt;     
        }
        private void btnThem_Click(object sender, EventArgs e)
        {
            try
            {
                dt = new DataTable();
                dt = LoadData("SELECT TenDangNhap FROM Login WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'");
                if (dt.Rows.Count > 0)
                {
                    MessageBox.Show("Tên đăng nhập đã tồn tại.");
                    return;
                }
                if (txtHoVaTen.Text == "")
                {
                    MessageBox.Show("Điền họ và tên.");
                    return;
                }
                if (txtTenDangNhap.Text == "")
                {
                    MessageBox.Show("Điền tên đăng nhập.");
                    return;
                }
                if (txtMatKhau.Text == "")
                {
                    MessageBox.Show("Điền mật khẩu.");
                    return;
                }
                cmd = new SqlCommand("INSERT INTO Login(TenDangNhap, MatKhau, hotenNguoiDangNhap, QuyenHanID, PhanHeKhongCapQuyen) VALUES(@tendangnhap, @matkhau, @hoten, @quyenhanID, @phanekpq)", con);
                cmd.Parameters.AddWithValue("@tendangnhap", txtTenDangNhap.Text);
                cmd.Parameters.AddWithValue("@matkhau", txtMatKhau.Text);
                cmd.Parameters.AddWithValue("@hoten", txtHoVaTen.Text);
                cmd.Parameters.AddWithValue("@quyenhanID", cbQuyenHan.SelectedValue);
                cmd.Parameters.AddWithValue("@phanekpq", "123456");
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                LoadGridview();
                MessageBox.Show("Thêm user thành công! Vui lòng chọn phân hệ cấp quyền.");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi ! " + ex);
            }
            
        }
        private void Chuyen()
        {
            try
            {
                if (lbTruocPhanQuyen.SelectedIndex < 0)
                {
                    return;
                }                
                dt = LoadData("SELECT PhanHeKhongCapQuyen FROM Login WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'");
                if (dt.Rows.Count == 0)
                {
                    lbTruocPhanQuyen.Items.Clear();
                }
                string phanheId = dt.Rows[0][0].ToString().Trim();
                string value = lbTruocPhanQuyen.SelectedValue.ToString();
                string phanheIdAf = phanheId.Replace(value, "");
                cmd = new SqlCommand("UPDATE Login SET PhanHeKhongCapQuyen = @phkCQ WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'", con);
                cmd.Parameters.AddWithValue("@phkCQ", phanheIdAf);
                cmd.ExecuteNonQuery();
                cmd.Dispose();

                dt = LoadData("SELECT PhanHeCapQuyen FROM Login WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'");
                phanheId = dt.Rows[0][0].ToString().Trim();
                phanheIdAf = String.Concat(phanheId, value);
                cmd = new SqlCommand("UPDATE Login SET PhanHeCapQuyen = @phCQ WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'", con);
                cmd.Parameters.AddWithValue("@phCQ", phanheIdAf);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                LBTruocPhanQuyen();
                LBSauPhanQuyen();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi ! " + ex);
            }
        }
        private void Lui()
        {
            try
            {
                if (lbSauPhanQuyen.SelectedIndex < 0)
                {
                    return;
                }
                dt = LoadData("SELECT PhanHeCapQuyen FROM Login WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'");
                string phanheId = dt.Rows[0][0].ToString().Trim();
                string value = lbSauPhanQuyen.SelectedValue.ToString();
                string phanheIdAf = phanheId.Replace(value, "");
                cmd = new SqlCommand("UPDATE Login SET PhanHeCapQuyen = @phCQ WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'", con);
                cmd.Parameters.AddWithValue("@phCQ", phanheIdAf);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                LBSauPhanQuyen();
                dt = LoadData("SELECT PhanHeKhongCapQuyen FROM Login WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'");
                phanheId = dt.Rows[0][0].ToString().Trim();
                phanheIdAf = String.Concat(phanheId, value);
                cmd = new SqlCommand("UPDATE Login SET PhanHeKhongCapQuyen = @phkCQ WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'", con);
                cmd.Parameters.AddWithValue("@phkCQ", phanheIdAf);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                LBTruocPhanQuyen();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi ! " + ex);
            }
        }
        private void btnChuyen_Click(object sender, EventArgs e)
        {
            Chuyen();
        }
      
        private void btnLui_Click(object sender, EventArgs e)
        {
            try
            {
                if (lbSauPhanQuyen.SelectedIndex < 0)
                {
                    return;
                }
                dt = LoadData("SELECT PhanHeCapQuyen FROM Login WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'");
                string phanheId = dt.Rows[0][0].ToString().Trim();
                string value = lbSauPhanQuyen.SelectedValue.ToString();
                string phanheIdAf = phanheId.Replace(value, "");
                cmd = new SqlCommand("UPDATE Login SET PhanHeCapQuyen = @phCQ WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'", con);
                cmd.Parameters.AddWithValue("@phCQ", phanheIdAf);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                LBSauPhanQuyen();
                dt = LoadData("SELECT PhanHeKhongCapQuyen FROM Login WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'");
                phanheId = dt.Rows[0][0].ToString().Trim();
                phanheIdAf = String.Concat(phanheId, value);
                cmd = new SqlCommand("UPDATE Login SET PhanHeKhongCapQuyen = @phkCQ WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'", con);
                cmd.Parameters.AddWithValue("@phkCQ", phanheIdAf);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                LBTruocPhanQuyen();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi ! "+ ex);
            }
            
        }

        private void cbQuyenHan_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbQuyenHan.SelectedIndex == 0)
            {
                gbPhanHe.Hide();
            }
            else
            {
                gbPhanHe.Show();
            }
        }

        private void btnSua_Click(object sender, EventArgs e)
        {
            try
            {
                dt = new DataTable();
                dt = LoadData("SELECT QuyenHanID FROM Login WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'");
                if (cbQuyenHan.SelectedValue.ToString() != dt.Rows[0]["QuyenHanID"].ToString())
                {
                    cmd = new SqlCommand("UPDATE Login SET MatKhau = @matkhau, hotenNguoiDangNhap = @hoten, QuyenHanID = @quyenhan WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'", con);
                    cmd.Parameters.AddWithValue("@matkhau", txtMatKhau.Text);
                    cmd.Parameters.AddWithValue("@hoten", txtHoVaTen.Text);
                    cmd.Parameters.AddWithValue("@quyenhan", cbQuyenHan.SelectedValue);
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    cmd = new SqlCommand("UPDATE Login SET PhanHeKhongCapQuyen = '123456', PhanHeCapQuyen = '' WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'", con);
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    LBTruocPhanQuyen();
                    LBSauPhanQuyen();
                    MessageBox.Show("Sửa thông tin thành công! Vui lòng chọn phân hệ để cấp quyền.");
                }
                else
                {
                    cmd = new SqlCommand("UPDATE Login SET MatKhau = @matkhau, hotenNguoiDangNhap = @hoten, QuyenHanID = @quyenhan WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'", con);
                    cmd.Parameters.AddWithValue("@matkhau", txtMatKhau.Text);
                    cmd.Parameters.AddWithValue("@hoten", txtHoVaTen.Text);
                    cmd.Parameters.AddWithValue("@quyenhan", cbQuyenHan.SelectedValue);
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    LBTruocPhanQuyen();
                    LBSauPhanQuyen();
                    MessageBox.Show("Sửa thông tin thành công! Vui lòng chọn phân hệ để cấp quyền.");
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show("Lỗi !" + ex);
            }
            
            
        }

        private void btnXoa_Click(object sender, EventArgs e)
        {
            try
            {
                cmd = new SqlCommand("DELETE FROM Login WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'", con);
                DialogResult dialRe = MessageBox.Show("Chắc xóa không ?", "Xác nhận xóa", MessageBoxButtons.YesNo);
                if (dialRe == DialogResult.Yes)
                {
                    cmd.ExecuteNonQuery();               
                    LoadGridview();
                    MessageBox.Show("Xóa user thành công! ");
                    txtHoVaTen.Text = "";
                    txtMatKhau.Text = "";
                    txtTenDangNhap.Text = "";
                    cbQuyenHan.SelectedValue = 1;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi ! " + ex);
            }
        }

       
        private void lbTruocPhanQuyen_DoubleClick(object sender, EventArgs e)
        {
            Chuyen();
        }

        private void lbSauPhanQuyen_DoubleClick(object sender, EventArgs e)
        {
            Lui();
        }

        private void btnChuyenHet_Click(object sender, EventArgs e)
        {
            try
            {
                if (lbTruocPhanQuyen.SelectedIndex < 0)
                {
                    return;
                }
                dt = LoadData("SELECT PhanHeKhongCapQuyen FROM Login WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'");
                if (dt.Rows.Count == 0)
                {
                    lbTruocPhanQuyen.Items.Clear();
                }
                string phanhekcq = dt.Rows[0][0].ToString().Trim();
                cmd = new SqlCommand("UPDATE Login SET PhanHeKhongCapQuyen = @phkCQ WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'", con);
                cmd.Parameters.AddWithValue("@phkCQ", "");
                cmd.ExecuteNonQuery();
                cmd.Dispose();

                dt = LoadData("SELECT PhanHeCapQuyen FROM Login WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'");
                string phanhecq = dt.Rows[0][0].ToString().Trim();
                string phanheIdAf = String.Concat(phanhecq, phanhekcq);
                cmd = new SqlCommand("UPDATE Login SET PhanHeCapQuyen = @phCQ WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'", con);
                cmd.Parameters.AddWithValue("@phCQ", phanheIdAf);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                LBTruocPhanQuyen();
                LBSauPhanQuyen();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi ! " + ex);
            }
        }

        private void btnLuiHet_Click(object sender, EventArgs e)
        {
            try
            {
                if (lbSauPhanQuyen.SelectedIndex < 0)
                {
                    return;
                }
                dt = LoadData("SELECT PhanHeCapQuyen FROM Login WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'");
                string phanhecq = dt.Rows[0][0].ToString().Trim();
                cmd = new SqlCommand("UPDATE Login SET PhanHeCapQuyen = @phCQ WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'", con);
                cmd.Parameters.AddWithValue("@phCQ", "");
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                dt = LoadData("SELECT PhanHeKhongCapQuyen FROM Login WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'");
                string phanhekcq = dt.Rows[0][0].ToString().Trim();
                string phanheIdAf = String.Concat(phanhekcq, phanhecq);
                cmd = new SqlCommand("UPDATE Login SET PhanHeKhongCapQuyen = @phkCQ WHERE TenDangNhap = '" + txtTenDangNhap.Text + "'", con);
                cmd.Parameters.AddWithValue("@phkCQ", phanheIdAf);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                LBTruocPhanQuyen();
                LBSauPhanQuyen();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi ! " + ex);
            }
        }

        private void frmPhanQuyen_FormClosing(object sender, FormClosingEventArgs e)
        {
            Application.Exit();
        }
       
    }
}
